# cluster
import time
from matplotlib.pyplot import axis 
import  numpy as np
import pandas as pd
import pymysql
class MysqlUtils(object):
    def __init__(self) -> None:
       self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password="root",
            port=3306,
            db="scenic",
            charset= "utf8mb4"
        )

    def get_data(self):
        """获取财务数据"""
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

        sql ="""
        select t.tourist_agency_name,rel.id_no, LEFT(rel.id_no, 2) as province_code, DAYOFWEEK(gate.create_time) as non_weekend, 
        cast(substring(rel.id_no, 7,4) as unsigned) as birth_year from ticket_order_user_rel rel join ticket_order t on t.id = rel.order_id join order_user_gate_rel gate
        on gate.ticket_rel_id = rel.id
        WHERE t.tourist_agency_name != '' and t.pay_time != '' 
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        #print(df.head)
        #数据清洗
        df['non_weekend'] = df['non_weekend'].apply(lambda x:1 if x not in[1,7] else 0)
        #新增有效性标记列
        df['valid_id'] = df['id_no'].apply(lambda x:1 if x  and str(x).strip() != '' else 0)
        #计算有记录的老年人及外省游客
        df['elderly_ratio'] = df.apply(
            lambda  x : 1 if (x['valid_id'] and  2025 - x['birth_year'] >= 60)else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        df['out_province_ratio'] = df.apply(
            lambda x : 1 if (x['valid_id']and   x['province_code'] != '44') else 0 if x['valid_id'] else np.nan, 
        axis=1
        )
        #print(df.head)

        #print(df.head)
        #分组聚合计算占比
        result = df.groupby(['tourist_agency_name']).agg(
            taotal_visitors=('id_no','count'),
            valid_visitors=('valid_id','sum'),
            out_province=('out_province_ratio','sum'),
            elderly=('elderly_ratio','sum'),
            non_weekend_ratio=('non_weekend','mean'),
        ).reset_index()
        #计算实际比例
        result['out_province_ratio']= result['out_province'] / result['valid_visitors'].replace(0, np.nan)
        result['elderly_ratio']= result['elderly'] / result['valid_visitors'].replace(0,np.nan)
        #清除中间列
        result = result.drop(['out_province','elderly'],axis=1)
        #print =(result.head) 
        result['out_province_ratio'] = result['out_province_ratio'].fillna(0)
        result['elderly_ration'] = result['elderly_ratio'].fillna(0)
        #print =(result.head)
        result.to_csv('scenic_data.csv')


if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()
